package ecustom.common.conn;

import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.log4j.Logger;

import com.alibaba.fastjson.JSONObject;

import ecustom.common.CustomMap;
import ecustom.common.HttpClient;
import ecustom.common.util.CustomUtil;
import ecustom.common.util.DESUtil;
import ecustom.common.util.PropUtil;
import weaver.conn.WeaverConnection;

/**
 * 数据库连接公共类。
 * 法律声明：非公开代码，仅供授权机构（公司或个人）使用，公开发表将追究法律责任。
 * @author 曹水涛
 */
public class DBAgent extends DB {
	
	private static final Logger log = Logger.getLogger(DBAgent.class);
	
	private String source;
	private String[] columnLabels;

	public DBAgent() {}
	
	public DBAgent(String dsName) {
		this.source = dsName;
	}
	
	public static DBAgent conn() {
		return new DBAgent();
	}
	
	public static DBAgent conn(String dsName) {
		return new DBAgent(dsName);
	}

	public CustomMap keyMap(String keyField, String sql, Object... whereValues) throws Exception {
		List<CustomMap> mapList = list(sql, whereValues);
		
		CustomMap keyMap = new CustomMap();
		if (mapList.isEmpty()) return keyMap;
		
		keyField = keyField.toUpperCase();
		
		for (CustomMap map : mapList) {
			String keyValue = CustomUtil.getString(map.get(keyField));
			if (CustomUtil.isBlank(keyValue)) continue ;
			keyMap.put(keyValue, map);
		}
		
		return keyMap;
	}

	public CustomMap one(String sql, Object... values) throws Exception {
		List<CustomMap> mapList = list(sql, values);
		if (mapList == null || mapList.isEmpty())
			return new CustomMap();
		return mapList.get(0);
	}
	
	public List<CustomMap> list(StringBuilder sql, Object... values) throws Exception {
		return list(sql.toString(), values);
	}

	public List<CustomMap> list(String sql, Object... values) throws Exception {
		try {
			if (log.isDebugEnabled()) log.debug("mapList(), sql = " + sql + ", " + DBUtil.joinValues(values));
			JSONObject res = post("list", sql, values);
			List<CustomMap> list = new ArrayList<>();
			for (JSONObject obj : res.getJSONArray("result").toJavaList(JSONObject.class)) {
				list.add(new CustomMap(obj));
			}
	        return list;
		} catch (Exception e) {
			throw new SQLException("DB access error", e);
		}
	}
	
	public List<String> getStringList(String sql, Object... values) throws Exception {
		List<CustomMap> mapList = list(sql, values);
		return convertList(mapList, String.class);
	}

	public <T> T find(Class<T> clazz, String whereSql, Object... values) throws Exception {
		return findBySql(clazz, DBUtil.getQuerySql(clazz, whereSql), values);
	}

	public <T> T findBySql(Class<T> clazz, String sql, Object... values) throws Exception {
		throw new Exception("findBySql 代理未实现");
	}

	public int save(Object o) throws Exception {
		CustomMap saveFields = DBUtil.getSaveFields(o);

		Set<String> fieldNames = saveFields.keySet();
		String sql = DBUtil.getInsertSql(fieldNames, o);
		
		Object[] values = new Object[fieldNames.size()];
		int valueIndex = 0;
		for (String fieldName : fieldNames) {
			values[valueIndex] = saveFields.get(fieldName);
			valueIndex++;
		}
		
		this.source = DBUtil.getDsName(o.getClass());
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public int save(String tableName, Map<String, Object> fieldsMap) throws Exception {
		Set<String> fieldNames = fieldsMap.keySet();
		String sql = DBUtil.getInsertSql(tableName, fieldNames);
		
		Object[] values = new Object[fieldNames.size()];
		int valueIndex = 0;
		for (String fieldName : fieldNames) {
			values[valueIndex] = fieldsMap.get(fieldName);
			valueIndex++;
		}
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public void saveCatch(Object o) throws Exception {
		try {
			CustomMap saveFields = DBUtil.getSaveFields(o);

			Set<String> fieldNames = saveFields.keySet();
			String sql = DBUtil.getInsertSql(fieldNames, o);

			Object[] values = new Object[fieldNames.size()];
			int valueIndex = 0;
			for (String fieldName : fieldNames) {
				values[valueIndex] = saveFields.get(fieldName);
				valueIndex++;
			}

			this.source = DBUtil.getDsName(o.getClass());
			_executeUpdate(sql, values);
		} catch (Exception e) {
			log.error("saveCatch()", e);
		} finally {
			DBUtil.returnConnection(this);
		}
	}
	
	public int update(Object o) throws Exception {
		CustomMap saveFields = DBUtil.getSaveFields(o);
		String idFieldName = DBUtil.getIdFieldName(o.getClass()).toUpperCase();
		Set<String> fieldNames = saveFields.keySet();
		fieldNames.remove(idFieldName);	// 移去ID字段

		String sql = DBUtil.getUpdateSql(fieldNames, o, idFieldName);
		
		Object[] values = new Object[fieldNames.size() + 1];
		int valueIndex = 0;
		for (String fieldName : fieldNames) {
			values[valueIndex++] = saveFields.get(fieldName);
		}
		values[valueIndex] = DBUtil.getIdFieldValue(o);
		
		this.source = DBUtil.getDsName(o.getClass());
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public int update(String tableName, Map<String, Object> updateFields, String whereSql, Object... whereValues) throws SQLException {
		String sql = "UPDATE " + tableName + " SET %s WHERE " + whereSql;
		
		whereValues = whereValues == null ? new Object[] {} : whereValues;
		
		Object[] values = new Object[updateFields.size() + whereValues.length];
		int index = 0;
		String sqlSet = "";
		for (String key : updateFields.keySet()) {
			values[index++] = updateFields.get(key);
			sqlSet += ", " + key + "=?";
		}
		for (Object value : whereValues) {
			values[index++] = value;
		}
		
		sql = String.format(sql, sqlSet.substring(2));
		return DBAgent.conn().executeUpdate(sql, values);
	}
	
	public int delete(String sql, Object... values) throws Exception {
		if (CustomUtil.isBlank(this.source)) {
			this.source = DBUtil.getDsName(Integer.class);
		}
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public <T> List<T> searchAll(Class<T> clazz) throws Exception {
		return searchBySql(clazz, DBUtil.getQuerySql(clazz, ""));
	}

	public <T> List<T> search(Class<T> clazz, String whereSql, Object... values) throws Exception {
		return searchBySql(clazz, DBUtil.getQuerySql(clazz, whereSql), values);
	}

	public <T> List<T> searchBySql(Class<T> clazz, String sql, Object... values) throws Exception {
		throw new Exception("searchBySql 代理未实现");
	}
	
	public List<Integer> getIntegerList(String sql, Object... values) throws Exception {
		return getIntegerList(sql.toString(), values);
	}
	
	public List<Integer> getIntegerList(StringBuilder sql, Object... values) throws Exception {
		return getIntegerList(sql.toString(), values);
	}
	
	public List<String> getStringList(StringBuilder sql, Object... values) throws Exception {
		return getStringList(sql.toString(), values);
	}
	
	public Integer getInteger(StringBuilder sql, Object... values) throws Exception {
		return getInteger(sql.toString(), values);
	}

	public Integer getInteger(String sql, Object... values) throws Exception {
		return findBySql(Integer.class, sql, values);
	}
	
	public Double getDouble(String sql, Object... values) throws Exception {
		return findBySql(Double.class, sql, values);
	}
	
	public Double getDouble(StringBuilder sql, Object... values) throws Exception {
		return findBySql(Double.class, sql.toString(), values);
	}
	
	public String getSource() {
		if (CustomUtil.isBlank(this.source)) {
			this.source = "ecology";
		}
		return this.source;
	}

	public WeaverConnection getConnection() throws SQLException {
		throw new SQLException("getConnection 代理未实现");
	}

	public PreparedStatement getStatement() {
		throw new RuntimeException("getStatement 代理未实现");
	}

	public ResultSet getResultSet() {
		throw new RuntimeException("getResultSet 代理未实现");
	}
	
	private int _executeUpdate(String sql, Object... values) throws SQLException {
		if (log.isDebugEnabled()) log.debug("_executeUpdate(), sql = " + sql + ", " + DBUtil.joinValues(values));
		try {
			JSONObject res = post("executeUpdate", sql, values);
	        return res.getIntValue("result");
		} catch (Exception e) {
			throw new SQLException("DB access error", e);
		}
	}

	public int executeUpdate(String sql, Object... values) throws SQLException {
		try {
			return _executeUpdate(sql, values);
		} finally {
			DBUtil.returnConnection(this);
		}
	}

	public CustomMap map(StringBuilder sql, Object... whereValues) throws Exception {
		return one(sql.toString(), whereValues);
	}

	public String getString(String sql, Object... values) throws Exception {
		return findBySql(String.class, sql, values);
	}

	public ResultSetMetaData getMetaData() {
		throw new RuntimeException("getMetaData 代理未实现");
	}

	public String[] getColumnLabels() {
		return columnLabels;
	}
	
	private JSONObject post(String method, String sql, Object... values) throws IOException {
		String contentUrl = PropUtil.getStringCK("evn.db.agent.url");
		String desKey = PropUtil.getStringCK("evn.db.agent.key");
		
		JSONObject req = new JSONObject();
		req.put("sql", sql);
		req.put("method", method);
		req.put("source", source);
		req.put("params", values);
		String postBody = req.toJSONString();
		if (log.isDebugEnabled()) log.debug("post body: " + postBody);
		String body = DESUtil.encryptBase64(postBody, desKey);
		String url = contentUrl + "/api/ecustom/agent/data";
		String resStr = new HttpClient()
				.setUrl(url)
				.setHeader("Content-Type", "text/plain")
				.post(body);
		JSONObject res = JSONObject.parseObject(resStr);
		Integer code = res.getInteger("code");
		if (code != 200) {
			if (log.isDebugEnabled()) log.debug("post() res: " + res);
			throw new RuntimeException(res.getString("message"));	
		}
		return res;
	}
	
	private <T> List<T> convertList(List<CustomMap> mapList, Class<T> clazz) {
		if (mapList == null || mapList.isEmpty())
			return new ArrayList<T>();
		
		List<T> list = new ArrayList<T>();
		for (CustomMap map : mapList) {
			list.add(convertMap(map, clazz));
		}
		return list;
	}
	
	private <T> T convertMap(CustomMap map, Class<T> clazz) {
		if (map == null || map.isEmpty())
			return null;
		
		for (Entry<String, Object> entry : map.entrySet()) {
			if (clazz == Double.class) return (T)CustomUtil.getDouble(entry.getValue());
			if (clazz == Integer.class) return (T)CustomUtil.getInteger(entry.getValue());
			if (clazz == String.class) return (T)CustomUtil.getString(entry.getValue());
			break ;
		}
		
		return null;
	}
}